# delimit;
clear;
capture log close;
set more off;
set mem 275m;
pause on;

local datafolder "\\Endeavor\Administration\Executive\CRR\Boxes\JP\Retirement_Prep_Survey\";
local logfiles "\\Endeavor\Administration\Executive\CRR\Boxes\JP\Retirement_Prep_Survey\";

use `datafolder'ret_prep_data.dta;

*education;
gen educ=q216;
replace educ=8 if educ==7;
replace educ=7 if educ==6;
replace educ=6 if educ==5;
replace educ=5 if educ==70;

gen HSORLESS=(educ<=3);
gen SOMECOLLEGE=(educ==4|educ==5);
gen COLLEGEPLUS=(educ>=6);
gen SOMECOLLEGEPLUS=SOMECOLLEGE==1|COLLEGEPLUS==1;

*race;
gen race=q244 if q244<94;
replace race=2 if race==8;
replace race=3 if race==7;
replace race=4 if race>3&race<94;

gen NONWHITE=(race!=1);
replace NONWHITE=. if race==94;

***gender (1=female);
gen GENDER=q102-1;

***marriage;
gen married=0 if q202<7;
replace married=1 if q202==2;
gen single=0 if q202<7;
replace single=1 if q202==1|q202==6;
gen divorce=0 if q202<7;
replace divorce=1 if q202==3|q202==4|q202==5;

gen COUPLE=(q202==2 | q202==6);

***age;
gen age=q106 if q106<14;
replace age=1 if age==10;
replace age=2 if age==11;
replace age=3 if age==12;
replace age=4 if age==13;

gen AGE=2006-q104;

***children in household;
gen CHILDREN=(q206>0);

***employment of respondent -- note the sample includes ALL households in which 
***the primary earner is not retired.  The respondent does not need to be the primary
***earner, but the respondent needs to have a say in major financial decisions of the
***household;

gen REMPLOYED= (q210m1==1|q210m2==1|q210m3==1);

**income;
gen INCOME=q232 if q232!=994;
set seed 1;
gen random=uniform();
gen IMPINCOME=random * 15000 if INCOME==1;
replace IMPINCOME=15000 + (random * (24999-15000)) if INCOME==2;
replace IMPINCOME=25000 + (random * (34999-25000)) if INCOME==3;
replace IMPINCOME=35000 + (random * (49999-35000)) if INCOME==4;
replace IMPINCOME=50000 + (random * (74999-50000)) if INCOME==5;
replace IMPINCOME=75000 + (random * (99999-75000)) if INCOME==6;
replace IMPINCOME=100000 + (random * (124999-100000)) if INCOME==7;
replace IMPINCOME=125000 + (random * (149999-125000)) if INCOME==8;
replace IMPINCOME=150000 + (random * (199999-150000)) if INCOME==9;
replace IMPINCOME=200000 + (random * (249999-200000)) if INCOME==10;
replace IMPINCOME=250000 + (random * (2500000-250000)) if INCOME==11;

***Home ownership;
gen HOME=q410;
gen homevalue=q610 if q610!=999999999;
gen mortgage=q615 if q615!=999999999;
gen NETHOME=homevalue-mortgage;
gen paidoff=mortgage==0 if mortgage!=.;

***Equity to Income Ratio;
gen EQINC = NETHOME/IMPINCOME if NETHOME!=.& IMPINCOME!=.;
gen LEVERAGE=mortgage/homevalue if homevalue>0 & homevalue!=. & mortgage >0 & mortgage!=.;
drop if LEVERAGE >10 & LEVERAGE!=.;
drop if EQINC >100 & EQINC!=.;

***Self reported Pension coverage;
gen DBPLAN=q505==1 if q505!=3;
gen DCPLAN=q510==1 if q510!=3;
gen DBONLY=(DBPLAN==1)*(DCPLAN==0) if q505!=3 & q510!=3;
gen DCONLY=(DBPLAN==0)*(DCPLAN==1) if q505!=3 & q510!=3;
gen BOTH=(DBPLAN==1)*(DCPLAN==1)   if q505!=3 & q510!=3;
gen PENSION=(DBPLAN+DCPLAN>0) if q505!=3 & q510!=3;

*** 401(K) + IRA balance;
gen DCBALANCE=q515 if q515!=999999999;
replace DCBALANCE=0 if q510==2;

*** Self-reported adequacy of expected retirement income;
gen INADEQUATE=q520<=2;

***************************************************************************************;
***************************************************************************************;
***************************************************************************************;

*** analysis questions;
gen paymortgage=(q620==1) if q620!=8;

*** purpose of the home;
gen PURPOSE=1 if (q632<=2 | q635<=2);
*PURPOSE1=SAFETY NET;
replace PURPOSE=2 if (q632==3 | q635==3);

replace PURPOSE=purpose if PURPOSE==.;

*PURPOSE:
*OTHER	6
*STAY	5
*INVESTMENT	4
*DOWNSIZE	3
*BEQUEST	2
*SAFETY	1;
***************************************************************************************;

*** will you use home equity in retirment;
gen USEHOMEQ=q625==1;
gen NOUSEHOMEQ=q625!=1;

*** age groups;
gen AGEGROUP=1 if AGE<55;
replace AGEGROUP=2 if AGE<60 & AGEGROUP==.;
replace AGEGROUP=3 if AGEGROUP==.;
label define AGEGROUP 1 "50-54" 2 "55-59" 3 "60-65";
label values AGEGROUP AGEGROUP  ;

***************************************************************************************;
***************************************************************************************;
***************************************************************************************;
log using `logfiles'tables-1.log, replace;
*Table 3 in issue in brief;
*Planning to use home equity for ordinary living expenses, by age group;
*Percent;
tab q625 AGEGROUP [aw=wt], co  nof;
*Count;
tab q625 AGEGROUP [aw=wt];

*Table 4 in issue in brief;
*Plan to tap equity, various approaches, by age group;
*Percent;
tab q630 AGEGROUP [aw=wt], co  nof;
*Count;
tab q630 AGEGROUP [aw=wt];

*Table 5 in issue in brief;
*Do Not Plan to tap equity, Intended use of home, by age group;
*Percent;
gen q635new=q635;
replace q635new=1 if q635==2;
label define q635new 1 "Insurance" 3 "Bequest" 4 "Other" 8 "Not Sure";
label values AGEGROUP AGEGROUP  ;

tab q635 AGEGROUP [aw=wt], co  nof;
*Count;
tab q635 AGEGROUP [aw=wt];

***************************************************************************************;
*Alternative tables;
*These tables take advantage of the "other" responses, when respondents answer an open 
*ended question;
***************************************************************************************;

*Table 3a in issue in brief (alternative, adding those that answered downsize later on);
*Planning to use home equity for ordinary living expenses, by age group;
gen q625a=q625;
replace q625a=1 if PURPOSE==3 & q625a!=1;
label values q625a q625  ;

*Percent;
tab q625a AGEGROUP [aw=wt], co  nof;
*Count;
tab q625a AGEGROUP [aw=wt];

*Table 4a in issue in brief (alternative, adding those that answered downsize later on);
*Plan to tap equity, various approaches, by age group;
gen q630a=q630;
replace q630a=1 if PURPOSE==3 & q630a==.;
label values q630a q630;
*Percent;
tab q630a AGEGROUP [aw=wt], co  nof;
*Count;
tab q630a AGEGROUP [aw=wt];

*Table 5 in issue in brief;
*Do Not Plan to tap equity, Intended use of home, by age group;
*PURPOSE: OTHER	6, STAY	5, INVESTMENT	4, DOWNSIZE	3, BEQUEST	2, SAFETY	1;
gen q635a=q635;
replace q635a=1 if q635a==2;
replace q635a=1 if PURPOSE==1 & q635!=.; /*really answered insurance*/;
replace q635a=3 if PURPOSE==2 & q635!=.; /*really answered bequest*/;
replace q635a=. if PURPOSE==3 & q635!=.; /*really answered downsize, meaning they will access equity*/; 
replace q635a=4 if 
       (PURPOSE==4|PURPOSE==5|PURPOSE==6) & q635!=.; /*really answered downsize, meaning they will access equity*/; 

label values q635a q635new;
	*Percent;
tab q635a AGEGROUP [aw=wt], co  nof;
*Count;
tab q635a AGEGROUP [aw=wt];
log close;
***************************************************************************************;
***************************************************************************************;
***************************************************************************************;
log using `logfiles'tables-2.log, replace;
*Basic Demographics;
tabstat AGE NONWHITE COUPLE HOME HSORLESS SOMECOLLEGE COLLEGEPLUS INADEQUATE EQINC [w=wt], stats(mean n)  f(%9.4f);

*Household income;
tab q232 [aw=wt];

*Housing value, by age;
gen withmortgage=(homevalue>0 & homevalue!=. & mortgage >0 & mortgage!=.);
label var withmortgage "1 if household has an outstanding mortgage, 0 otherwise";


*Mean;
tabstat NETHOME homevalue mortgage [w=wt] if (mortgage!=. &  homevalue!=.), by(AGEGROUP) stats(mean)  f(%9.0f);
*Median;
tabstat NETHOME homevalue mortgage [w=wt] if (mortgage!=. &  homevalue!=.), by(AGEGROUP) stats(p50)  f(%9.0f);
*Percents;
tabstat LEVERAGE withmortgage EQINC [w=wt] if (mortgage!=. &  homevalue!=.), by(AGEGROUP) stats(mean) f(%9.4f);

*Pension Coverage;
tabstat PENSION DBPLAN DCPLAN DBONLY DCONLY BOTH [w=wt], stats(mean n)  f(%9.4f);

*Income-Wealth;
tabstat IMPINCOME NETHOME homevalue mortgage  DCBALANCE   [w=wt], stats(mean p50 n)  f(%9.0f);

tabstat DCBALANCE if DCBALANCE!=0   [w=wt], stats(mean p50 n)  f(%9.0f) column(variables);
tabstat mortgage if mortgage!=0   [w=wt], stats(mean p50 n)  f(%9.0f) column(variables);

***************************************************************************************;
*Sample comparisons: q625 (q625a)==1 vs. q625 (q625a)==0 ,USE home equity or not  ;
gen dq625=q625==1; gen dq625a=q625a==1;
*dq625= T-Test: will use home equity to finance living expenses;
*dq625a= T-Test: will use home equity to finance living expenses, alternative definition, including those that
*		 answered "downsize";

*dollar amounts;
tabstat NETHOME AGE homevalue mortgage DCBALANCE [w=wt], by(dq625) stats(mean)  f(%9.4f);
tabstat NETHOME AGE homevalue mortgage DCBALANCE [w=wt], by(dq625) stats(p50)  f(%9.0f);
tabstat DCBALANCE if DCBALANCE>0 [w=wt], by(dq625) stats(mean)  f(%9.4f)  column(variables);
*Percents;
tabstat INADEQUATE withmortgage DCPLAN DBPLAN COLLEGEPLUS NONWHITE GENDER COUPLE [w=wt], by(dq625) stats(mean) f(%9.4f);
*Check leverage ratio;
tabstat LEVERAGE [w=wt] if (mortgage!=. &  homevalue!=.), by(dq625) stats(mean p50) f(%9.4f);

****************************************************************************************;
*Alternative definition;
*dollar amounts;
tabstat NETHOME AGE homevalue mortgage [w=wt], by(dq625a) stats(mean)  f(%9.4f);
tabstat NETHOME AGE homevalue mortgage [w=wt], by(dq625a) stats(p50)  f(%9.0f);
tabstat DCBALANCE if DCBALANCE>0 [w=wt], by(dq625) stats(mean)  f(%9.4f)  column(variables);

*Percents;
tabstat INADEQUATE withmortgage DCPLAN DBPLAN COLLEGEPLUS NONWHITE GENDER COUPLE [w=wt], by(dq625a) stats(mean) f(%9.4f);
*Check leverage ratio;
tabstat LEVERAGE [w=wt] if (mortgage!=. &  homevalue!=.), by(dq625a) stats(mean p50) f(%9.4f);
log close;

***************************************************************************************;
log using `logfiles'tests.log, replace;
**T-Test 'use home' averages against 'not use home' averages;
*dq625;
hotelling INADEQUATE [w=wt], by (dq625);
hotelling NETHOME [w=wt], by (dq625);
hotelling withmortgage [w=wt], by (dq625);
hotelling AGE [w=wt], by (dq625);
hotelling DCPLAN [w=wt], by (dq625);
hotelling DBPLAN [w=wt], by (dq625);
hotelling homevalue [w=wt], by (dq625);
hotelling mortgage [w=wt], by (dq625);

hotelling COLLEGEPLUS [w=wt], by (dq625);
hotelling NONWHITE [w=wt], by (dq625);
hotelling GENDER [w=wt], by (dq625);
hotelling COUPLE [w=wt], by (dq625);

*dq625a - alternative definition;
hotelling INADEQUATE [w=wt], by (dq625a);
hotelling NETHOME [w=wt], by (dq625a);
hotelling withmortgage [w=wt], by (dq625a);
hotelling AGE [w=wt], by (dq625a);
hotelling DCPLAN [w=wt], by (dq625a);
hotelling DBPLAN [w=wt], by (dq625a);
hotelling homevalue [w=wt], by (dq625a);
hotelling mortgage [w=wt], by (dq625a);

hotelling COLLEGEPLUS [w=wt], by (dq625a);
hotelling NONWHITE [w=wt], by (dq625a);
hotelling GENDER [w=wt], by (dq625a);
hotelling COUPLE [w=wt], by (dq625a);
*Signigicant differences in means:
*INADEQUATE, NETHOME, withmortgage home, value, college plus, nonwhite;
log close;
***************************************************************************************;
log using `logfiles'regression.log, replace;
*Probit Regressions - check whether the differences are still significant while controlling for 
*other factors;

*Change scale of dollar amounts;
replace homevalue=homevalue/100000;
replace mortgage=mortgage/100000;
replace IMPINCOME=IMPINCOME/100000;
replace DCBALANCE=DCBALANCE/100000;
replace NETHOME=NETHOME/100000;

*Using home value and mortgage;
dprobit dq625 INADEQUATE homevalue mortgage DCPLAN DBPLAN AGE IMPINCOME COLLEGEPLUS NONWHITE COUPLE;
dprobit dq625a INADEQUATE homevalue mortgage DCPLAN DBPLAN AGE IMPINCOME COLLEGEPLUS NONWHITE COUPLE;

*Using net home value and dummy for presence of mortgage;
dprobit dq625 INADEQUATE  withmortgage NETHOME DCPLAN DBPLAN AGE IMPINCOME COLLEGEPLUS NONWHITE COUPLE;
dprobit dq625a INADEQUATE withmortgage NETHOME DCPLAN DBPLAN AGE IMPINCOME COLLEGEPLUS NONWHITE COUPLE;

*Significant: Inadequate= positive relationship, Nethome or homevalue=positive, DCPLAN=positive,
*              withmortgage dummy=positive, AGE=positive (marginally);

*Not Significant: DBPLAN=negative, outstanding mortgage=negative, Imputed income=positive, 
*					Some college or more= positive, nonwhite=positive, couple=negative;

***************************************************************************************;
***************************************************************************************;
***************************************************************************************;
log close;

